library(dplyr)
library(stringr)
library(tibble)
library(DT)
library(htmltools)
library(scales)
extract_state <- function(name) str_match(name, ", ([A-Z]{2})")[, 2]
state_df <- tibble(
abb = c(state.abb, "DC", "PR"),
name = c(state.name, "District of Columbia", "Puerto Rico")
)
highlight <- function(x) paste0('<span style="color:#0000ff; font-weight:bold;">', x, '</span>')
q2_3_tbl <- INCOME %>%
filter(year == 2015) %>%
left_join(HOUSEHOLDS %>% filter(year == 2015),
by = c("GEOID", "NAME", "year")) %>%
left_join(POPULATION %>% filter(year == 2015),
by = c("GEOID", "NAME", "year")) %>%
mutate(
state = extract_state(NAME),
total_income = household_income * households
) %>%
group_by(state) %>%
summarise(
total_income = sum(total_income, na.rm = TRUE),
total_population = sum(population, na.rm = TRUE),
avg_individual_income = total_income / total_population,
.groups = "drop"
) %>%
left_join(state_df, by = c("state" = "abb")) %>%
filter(!is.na(state)) %>%
arrange(desc(avg_individual_income)) %>%
transmute(
State = name,
Abbrev = state,
`Avg Individual Income (2015)` = avg_individual_income,
Population = total_population,
`Total Income` = total_income
)
# highlight
top_state <- q2_3_tbl$Abbrev[1]
q2_3_tbl <- q2_3_tbl %>%
mutate(State = ifelse(Abbrev == top_state, highlight(State), State))
# export functions with table
datatable(
q2_3_tbl %>%
mutate(
`Avg Individual Income (2015)` = dollar(`Avg Individual Income (2015)`),
`Total Income` = dollar(`Total Income`),
Population = comma(Population)
),
rownames = FALSE,
escape = FALSE,
class = "compact stripe hover order-column nowrap",
caption = tags$caption(
style = "caption-side: top; text-align: left; font-weight:600;",
"Average Individual Income By State In The Year 2015"
),
extensions = "Buttons",
options = list(
pageLength = 10,
dom = "Bfrtip",
buttons = c("copy", "csv", "excel"),
order = list(list(2, "desc"))
)
)
